capture log close
clear all
set maxvar 30000
set more off
pause off
# delimit;

local crime "";
local output "";

/* 
Table I
RD and IV estimates of effects on criminal justice outcomes
*/


/* ********************************************** */
/* **** FIRST STAGE AND EMPLOYMENT VARIABLES **** */
/* ********************************************** */

use "`crime'/prwora_crime_summstats.dta", clear ;

keep if label == "prec_rd_mean" ;

local pre_all "a" ;

preserve ;

keep	`pre_all'age18_yes `pre_all'n_age18unfav `pre_all'n_onssi1922 			
		`pre_all'avg_kidemp5* `pre_all'avg_kidemp15* `pre_all'avg_kidearn
;

xpose, clear varname ;

rename v1 mean ;
rename _varname variables ;
order variables mean ;

replace variables = subinstr(variables,"_1997_2017", "", .) ;
replace variables = subinstr(variables,"`pre_all'n_", "", .) ;
replace variables = "age18_yes" if variables == "`pre_all'age18_yes" ; 
replace variables = "avg_kidemp5" if variables == "`pre_all'avg_kidemp5" ; 
replace variables = "avg_kidemp15" if variables == "`pre_all'avg_kidemp15" ; 
replace variables = "avg_kidearn" if variables == "`pre_all'avg_kidearn" ; 
		
tempfile all_mean ;
save `all_mean' ;

restore ;


clear;

foreach method in rd iv {;
			use "`crime'/prwora_bw264_crime_poly1allcov_`method'_all.dta" ;

			#delim;

			keep if variables == "age18_yes" | variables == "n_age18unfav" 
				  | variables == "avg_kidemp5_1997_2017" | variables == "avg_kidemp15_1997_2017" 
				  | variables == "avg_kidearn_1997_2017" | variables == "n_onssi1922" ;

			
			if "`method'" == "rd" {;

				keep variables n_post sen_post N ;
			
			};
			
			else {;
			
				keep if variables == "avg_kidemp5_1997_2017" | variables == "avg_kidemp15_1997_2017" 
					  | variables == "avg_kidearn_1997_2017" ;
				
				keep variables n_age18unfav sen_age18unfav N;
			};

			replace variables = subinstr(variables,"_1997_2017", "", .) ;
			replace variables = subinstr(variables,"n_", "", .) ;

			#delim;

			if "`method'" == "rd" {;
			
			local crime "age18_yes age18unfav onssi1922 
						 avg_kidemp5 avg_kidemp15 avg_kidearn" ;

			};
			
			else {;
			
				local crime "avg_kidemp5 avg_kidemp15 avg_kidearn" ;

			
			};
						 

			merge 1:1 variables using `all_mean' ;
			drop _merge ;

			gen flag_int = strpos(variables, "t") ;
			gen length = length(variables) ;

			replace flag_int = 1 if flag_int != 0 & length == 5;
			replace flag_int = 0 if flag_int != 1 ;
			replace flag_int = 1 if variables == "avg_kidearn" ;

			keep if inlist(variables, "age18_yes","age18unfav","onssi1922","avg_kidemp5","avg_kidemp15","avg_kidearn") ;
			
			replace variables = "Age 18 medical review" if variables == "age18_yes" ;
			replace variables = "Unfavorable review" if variables == "age18unfav" ;
			replace variables = "On SSI from ages 19-22" if variables == "onssi1922" ;
			
			replace variables = "Annual employment (earnings > $5K/yr)" if variables == "avg_kidemp5"  ;
			replace variables = "Annual employment (earnings > $15K/yr)" if variables == "avg_kidemp15" ;
			replace variables = "Annual earnings" if variables == "avg_kidearn" ;

			sort variables flag_int ;

			if "`method'" == "rd" {;

				rename n_post pt_est_rd ;
				rename sen_post se_rd ;
				gen star_rd = 2 * (normal(-abs(pt_est_rd/se_rd))) ;
				gen sta_sign_rd = "" ;
				replace sta_sign_rd = "*" if star_rd <= 0.1 & star_rd > 0.05 ;
				replace sta_sign_rd = "**" if star_rd <= 0.05 & star_rd > 0.01 ;
				replace sta_sign_rd = "***" if star_rd <= 0.01 ;
				drop star_rd ;
			};
			
			else {;
				
				rename n_age18unfav pt_est_iv ;
				rename sen_age18unfav se_iv ;
				gen star_iv = 2 * (normal(-abs(pt_est_iv/se_iv))) ;
				gen sta_sign_iv = "" ;
				replace sta_sign_iv = "*" if star_iv <= 0.1 & star_iv > 0.05 ;
				replace sta_sign_iv = "**" if star_iv <= 0.05 & star_iv > 0.01 ;
				replace sta_sign_iv = "***" if star_iv <= 0.01 ;
				drop star_iv ;
			};

			
			order variables pt_est* se* sta_sign* mean N flag_int;
			
			
			tostring flag_int, replace ;
			
			replace flag_int = "_in" if flag_int == "1" ;
			replace flag_int = "_ex" if flag_int == "0" ;
			
			drop length ;
			
			reshape wide pt_est* se* sta_sign* mean N, i(variables) j(flag_int) string ;

			tostring se*, replace ;
			replace se_`method'_ex = "(" + se_`method'_ex + ")" ;
			replace se_`method'_in = "(" + se_`method'_in + ")" ;
			
			tempfile all_`method'_all_I ;
			save `all_`method'_all_I' ;
			
}; 
		
		use `all_rd_all_I', clear ;
		
		merge 1:1 variables using `all_iv_all_I' ;
		drop _merge ;
		
		
		order variables pt_est_rd_ex sta_sign_rd_ex se_rd_ex pt_est_iv_ex sta_sign_iv_ex se_iv_ex mean_ex N_ex
						pt_est_rd_in sta_sign_rd_in se_rd_in pt_est_iv_in sta_sign_iv_in se_iv_in mean_in N_in ;
		
		
		gen order = 1 if variables == "Age 18 medical review" ;
		replace order = 2 if variables == "Unfavorable review" ;
		replace order = 3 if variables == "On SSI from ages 19-22" ;
		replace order = 21 if variables == "Annual employment (earnings > $5K/yr)" ;
		replace order = 22 if variables == "Annual employment (earnings > $15K/yr)" ;
		replace order = 23 if variables == "Annual earnings" ;
	
		sort order ;
		
tempfile part_I ;
save `part_I' ;		
		
		
/* ************************* */
/* **** CRIME VARIABLES **** */
/* ************************* */

use "`crime'/prwora_crime_summstats_exactage.dta", clear ;

keep if label == "prec_rd_mean" ;

local pre_all "a" ;

preserve ;

		keep	`pre_all'n_crime2char* `pre_all'n_crime2genr*
			    `pre_all'n_crime2nogn* `pre_all'n_crime2prbg*
			    `pre_all'n_crime2prtf* `pre_all'n_crime2prfd* 
				`pre_all'n_crime2vrob* `pre_all'n_crime2dgsl* 
				`pre_all'n_crime2vcps*
				
				`pre_all'n_crime2nitp* `pre_all'n_crime2vnrb*
				`pre_all'n_crime2nidp* `pre_all'n_crime2nidu* 
				`pre_all'n_crime2nibd* 
				
				`pre_all'n_crime2ahinc* `pre_all'n_crime2shinc* 
				`pre_all'n_crime2chinc* `pre_all'n_crime2ipp* 
				`pre_all'n_crime2sipp*
				;

		xpose, clear varname ;

		rename v1 mean ;
		rename _varname variables ;
		order variables mean ;

		drop if strpos(variables,"18_40") ;
		replace variables = subinstr(variables,"_18_38", "", .) ;
		replace variables = subinstr(variables,"`pre_all'n_crime2", "", .) ;
		replace variables = subinstr(variables,"`pre_all'n_", "", .) ;

		tempfile all_mean ;
		save `all_mean' ;

restore ;


clear;

foreach method in rd iv {;
			use "`crime'/prworaexact_bw264_crime_poly1allcov_`method'_all.dta" ;

			#delim;
			gen n_crime2 = strpos(variables, "n_crime2") ;

			keep if n_crime2 == 1 | variables == "age18_yes" | variables == "n_age18unfav" ;

			
			if "`method'" == "rd" {;

				keep variables n_post sen_post N ;
			
			};
			
			else {;
			
				keep if n_crime2 == 1  ;
				
				keep variables n_age18unfav sen_age18unfav N;
			};

			replace variables = subinstr(variables,"_18_38", "", .) ;
			replace variables = subinstr(variables,"n_crime2", "", .) ;
			replace variables = subinstr(variables,"n_", "", .) ;

			#delim;

			if "`method'" == "rd" {;
			
			local crime "age18_yes age18unfav onssi1922 char genr nogn
						 prbg prtf prfd vrob dgsl vcps vnrb nitp nidp 
						 nidu ipp sipp ahinc shinc nibd avg_kidemp5 
						 avg_kidemp15 avg_kidearn chinc" ;

			};
			
			else {;
			
				local crime "char genr nogn prbg prtf prfd vrob dgsl vnrb 
							 vcps nitp nidp nidu ipp sipp ahinc shinc nibd
							 avg_kidemp5 avg_kidemp15 avg_kidearn chinc" ;

			
			};
						 
			gen keep = strpos(variables, "char") ;

			foreach var in `crime' {;

				replace keep = strpos(variables, "`var'") if keep == 0;

			};

			drop if keep == 0 ;
			drop keep ;


			merge 1:1 variables using `all_mean' ;
			drop _merge ;


			gen flag_int = strpos(variables, "t") ;
			gen length = length(variables) ;
			replace flag_int = 1 if variables == "shinc" | variables == "sipp" ;
			replace flag_int = 0 if variables == "chinc" ;
			replace flag_int = 1 if flag_int != 0 & length == 5;
			replace flag_int = 0 if flag_int != 1 ;
			drop if strpos(variables,"_18_40") ;
			drop if inlist(variables,"aipp","cipp","ippt") ;

			replace variables = "Any charge" if variables == "char" | variables == "chart" ;
			replace variables = "Incarcerated (annual likelihood/days)" if variables == "ahinc" | variables == "shinc" ;
			replace variables = "Ever Incarcerated" if variables == "chinc" ;
			replace variables = "Incarceration/parole/probation (ann likelihood/days)" if variables == "ipp" | variables == "sipp" ; 
			
			replace variables = "Total (income generating)" if variables == "genr" | variables == "genrt" ;
			replace variables = "Burglary" if variables == "prbg" | variables == "prbgt" ;
			replace variables = "Theft" if variables == "prtf" | variables == "prtft" ;
			replace variables = "Forgery/fraud" if variables == "prfd" | variables == "prfdt" ;
			replace variables = "Robbery" if variables == "vrob" | variables == "vrobt" ;
			replace variables = "Drug distribution" if variables == "dgsl" | variables == "dgslt" ;
			replace variables = "Prostitution" if variables == "vcps" | variables == "vcpst" ;
			
			replace variables = "Total (non-income generating)" if variables == "nogn" | variables == "nognt" ;	
			replace variables = "Non-robbery violent" if variables == "vnrb"  | variables == "vnrbt" ;
			replace variables = "Disorderly conduct/obstruction/resisting arrest" if variables == "nibd" | variables == "nibdt" ;
			replace variables = "Criminal trespass" if variables == "nitp" | variables == "nitpt" ;
			replace variables = "Drug possession or paraphernalia" if variables == "nidp" | variables == "nidpt" ;
			replace variables = "Driving under the influence" if variables == "nidu" | variables == "nidut" ;
			
			
			sort variables flag_int ;

			if "`method'" == "rd" {;

				rename n_post pt_est_rd ;
				rename sen_post se_rd ;
				gen star_rd = 2 * (normal(-abs(pt_est_rd/se_rd))) ;
				gen sta_sign_rd = "" ;
				replace sta_sign_rd = "*" if star_rd <= 0.1 & star_rd > 0.05 ;
				replace sta_sign_rd = "**" if star_rd <= 0.05 & star_rd > 0.01 ;
				replace sta_sign_rd = "***" if star_rd <= 0.01 ;
				drop star_rd ;
			};
			
			else {;
				
				rename n_age18unfav pt_est_iv ;
				rename sen_age18unfav se_iv ;
				gen star_iv = 2 * (normal(-abs(pt_est_iv/se_iv))) ;
				gen sta_sign_iv = "" ;
				replace sta_sign_iv = "*" if star_iv <= 0.1 & star_iv > 0.05 ;
				replace sta_sign_iv = "**" if star_iv <= 0.05 & star_iv > 0.01 ;
				replace sta_sign_iv = "***" if star_iv <= 0.01 ;
				drop star_iv ;
			};

			
			order variables pt_est* se* sta_sign* mean N flag_int;
			
			
			tostring flag_int, replace ;
			
			replace flag_int = "_in" if flag_int == "1" ;
			replace flag_int = "_ex" if flag_int == "0" ;
			
			drop length ;
			
			reshape wide pt_est* se* sta_sign* mean N, i(variables) j(flag_int) string ;
	
			tostring se*, replace ;
			replace se_`method'_ex = "(" + se_`method'_ex + ")" ;
			replace se_`method'_in = "(" + se_`method'_in + ")" ;
			
			tempfile all_`method'_all ;
			save `all_`method'_all' ;

}; 
		
use `all_rd_all', clear ;
		
		merge 1:1 variables using `all_iv_all' ;
		drop _merge ;
		
		
		order variables pt_est_rd_ex sta_sign_rd_ex se_rd_ex pt_est_iv_ex sta_sign_iv_ex se_iv_ex mean_ex N_ex
						pt_est_rd_in sta_sign_rd_in se_rd_in pt_est_iv_in sta_sign_iv_in se_iv_in mean_in N_in ;
		
		
		gen order = 4 if variables == "Any charge" ;
		replace order = 5 if variables == "Incarcerated (annual likelihood/days)" ;
		replace order = 6 if variables == "Ever Incarcerated" ;
		replace order = 7 if variables == "Incarceration/parole/probation (ann likelihood/days)" ;
		replace order = 8 if variables == "Total (income generating)" ;
		replace order = 9 if variables == "Burglary" ;
		replace order = 10 if variables == "Theft" ;
		replace order = 11 if variables == "Forgery/fraud" ;
		replace order = 12 if variables == "Robbery" ;
		replace order = 13 if variables == "Drug distribution" ;
		replace order = 14 if variables == "Prostitution" ;
		replace order = 15 if variables == "Total (non-income generating)" ;
		replace order = 16 if variables == "Non-robbery violent" ;
		replace order = 17 if variables == "Disorderly conduct/obstruction/resisting arrest" ;
		replace order = 18 if variables == "Criminal trespass" ;
		replace order = 19 if variables == "Drug possession or paraphernalia" ;
		replace order = 20 if variables == "Driving under the influence" ;

/* *********************** */
/* **** ALL VARIABLES **** */
/* *********************** */
		
append using `part_I' ;
		
sort order ;
		
drop order ;

export excel using "`output'/tabB1.xls", firstrow(variables) ;
		
